﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using CMISLibrary.Entities;
using CMISLibrary;
using System.Globalization;
using BCNPCObject.DB;

namespace BCNPCObject
{
    public class cls_CN_THEODOINO_Controller
    {
        #region   Atributes

        private DataSet CMIS_Header = new CMISOutputParameter();
        private DataSet ds = new DataSet();
        private DataTable dt = new DataTable();
        private cls_CN_THEODOINO_Info info;
        private List<CN_THEODOINO> infolst;

        #endregion

        #region   Constructor

        public cls_CN_THEODOINO_Controller()
        {
            info = new cls_CN_THEODOINO_Info();
            infolst = new List<CN_THEODOINO>();
        }
        public cls_CN_THEODOINO_Controller(cls_CN_THEODOINO_Info Info)
        {
            this.info = Info;
        }
        public cls_CN_THEODOINO_Controller(List<CN_THEODOINO> Infolst, int i)
        {
            this.infolst = Infolst;
        }
        #endregion

        #region   Properties

        public cls_CN_THEODOINO_Info pInfor
        {
            get { return info; }
            set { info = value; }
        }
        public List<CN_THEODOINO> pInfoLst
        {
            get { return infolst; }
            set { infolst = value; }
        }

       
        #endregion

        #region Method DUNGNT-NPC
        public DataSet get_dsKH_thuATM_TNGAN(string strMadvi, string strMaTNGAN, DateTime? pNgaythu_tu, DateTime? pNgaythu_den)
        {
            try
            {
                CMIS2.ResetDB();
                List<NPC_HDON_TKHOAN> npc_hdon_tkhoan_list = new List<NPC_HDON_TKHOAN>();
                List<NPC_GDICH_ATM> npc_gdich_atm_list = new List<NPC_GDICH_ATM>();
                List<CN_THEODOINO> cn_theodoino_list = new List<CN_THEODOINO>();
                Int64[] arrID_HDON;


                npc_hdon_tkhoan_list = (from npctk in CMIS2.DB.NPC_HDON_TKHOAN
                                        where npctk.MA_DVIQLY == strMadvi
                                            //&& npctk.TINH_TRANG == 1
                                        && npctk.NGAY_NOP >= pNgaythu_tu
                                        && npctk.NGAY_NOP <= pNgaythu_den
                                        //join npcgdich in CMIS2.DB.NPC_GDICH_ATM
                                        select npctk).ToList();

                if (npc_hdon_tkhoan_list.Count == 0)
                { return null; }
                arrID_HDON = npc_hdon_tkhoan_list.Select(c => c.ID_HDON).ToArray();
                cn_theodoino_list = (from npcno in CMIS2.DB.CN_THEODOINO
                                     where npcno.MA_DVIQLY == strMadvi
                                     && arrID_HDON.Contains(npcno.ID_HDON)
                                         //&& npcno.TIEN_NO == 0 && npcno.THUE_NO == 0
                                     && npcno.TTRANG_SSAI == 0
                                     select npcno).ToList();

                npc_gdich_atm_list = (from npcgdich in CMIS2.DB.NPC_GDICH_ATM
                                      where npcgdich.MA_DVIQLY == strMadvi
                                      && arrID_HDON.Contains(npcgdich.ID_HDON)
                                      && npcgdich.MA_HUYGD == null
                                      && npcgdich.NGAY_TTOAN > pNgaythu_tu
                                      && npcgdich.NGAY_TTOAN <= pNgaythu_den
                                      select npcgdich).ToList();

                var gth = (from p in CMIS2.DB.CN_HDON_GTHU
                           where p.MA_DVIQLY == strMadvi && arrID_HDON.Contains(p.ID_HDON)
                           select p).ToList();
                var maxGT = gth.GroupBy(c => new { c.MA_DVIQLY, c.ID_HDON }).Select(k => new { k.Key.MA_DVIQLY, k.Key.ID_HDON, SO_BBGIAO = k.Max(m => m.SO_BBGIAO) }).ToList();
                var gthu = (from p in gth
                            join q in maxGT
                            on new { p.MA_DVIQLY, p.ID_HDON, p.SO_BBGIAO } equals new { q.MA_DVIQLY, q.ID_HDON, q.SO_BBGIAO }
                            select p).ToList();
                var bban = (from q in CMIS2.DB.CN_BB_GTHU
                            where q.MA_DVIQLY == strMadvi && (q.MA_TNGAN == strMaTNGAN || strMaTNGAN.Length == 0)
                            select q).ToList();
                var npc_gthu = (from p in maxGT
                                join q in bban
                                on new { p.MA_DVIQLY, p.SO_BBGIAO } equals new { q.MA_DVIQLY, q.SO_BBGIAO }
                                select new
                                {
                                    p.MA_DVIQLY,
                                    p.ID_HDON,
                                    q.SO_BBGIAO,
                                    q.MA_NVIEN,
                                    q.MA_TNGAN
                                }
                                ).ToList();
                var lst_tngan = (from n in CMIS2.DB.D_TNGAN_VIEN
                                 where n.MA_DVIQLY == strMadvi
                                 select n).ToList();

                var dskhthu = from tk in npc_hdon_tkhoan_list
                              join cn in cn_theodoino_list
                              on new { tk.MA_DVIQLY, tk.ID_HDON }
                              equals new { cn.MA_DVIQLY, cn.ID_HDON }
                              join gd in npc_gdich_atm_list
                              on new { tk.MA_DVIQLY, tk.MA_GD }
                              equals new { gd.MA_DVIQLY, gd.MA_GD }
                              join gt in npc_gthu
                              on new { tk.MA_DVIQLY, tk.ID_HDON }
                              equals new { gt.MA_DVIQLY, gt.ID_HDON }
                              orderby cn.MA_SOGCS, cn.STT
                              select new
                              {
                                  CHON = 1,
                                  cn.MA_DVIQLY,
                                  cn.MA_KHANG,
                                  gt.MA_TNGAN,
                                  //MA_TNGAN = "001",
                                  TEN_TNGAN = (from tn in lst_tngan where tn.MA_TNGAN == gt.MA_TNGAN select tn.TEN_TNGAN).FirstOrDefault(),
                                  cn.TEN_KHANG,
                                  cn.TTRANG_SSAI,
                                  cn.STT,
                                  cn.DCHI_KHANG,
                                  cn.LOAI_HDON,
                                  cn.ID_HDON,
                                  gd.MA_GD,
                                  cn.MA_PTTT,
                                  cn.TTHAI_GIAO,
                                  THANG_NAM = cn.THANG + "/" + cn.NAM,
                                  cn.MA_SOGCS,
                                  cn.TIEN_NO,
                                  cn.THUE_NO,
                                  TTIEN_NO = cn.THUE_NO + cn.TIEN_NO,
                                  tk.NGAY_NOP,
                                  tk.SO_TIEN,
                                  tk.TIEN_GTGT,
                                  TTIEN_NOP = tk.SO_TIEN + tk.TIEN_GTGT,
                                  tk.TINH_TRANG
                              }
                              ;
                DataSet dsdsthutien = new DataSet();
                DataTable dtdsthutien = new DataTable();
                dtdsthutien = Utility.LINQToDataTable(dskhthu);
                dtdsthutien.TableName = "DSCHAMNO";
                dsdsthutien.Tables.Add(dtdsthutien);
                return dsdsthutien;
            }
            catch
            { return null; }
        }
        public DataSet get_dsKH_thuATM(string strMadvi, string strMaNH, string strMAPTTT, DateTime? pNgaythu_tu, DateTime? pNgaythu_den, int i_ttrang_no)
        {
            try
            {
                CMIS2.ResetDB();
                List<NPC_HDON_TKHOAN> npc_hdon_tkhoan_list = new List<NPC_HDON_TKHOAN>();
                List<NPC_GDICH_ATM> npc_gdich_atm_list = new List<NPC_GDICH_ATM>();
                List<CN_THEODOINO> cn_theodoino_list = new List<CN_THEODOINO>();
                Int64[] arrID_HDON;

                if (i_ttrang_no == 0)//chua cham
                {

                    npc_hdon_tkhoan_list = (from npctk in CMIS2.DB.NPC_HDON_TKHOAN
                                            where npctk.MA_DVIQLY == strMadvi
                                            && npctk.TINH_TRANG == 0
                                            && (npctk.MA_NHANG == strMaNH || strMaNH.Length == 0)
                                            && npctk.NGAY_NOP >= pNgaythu_tu
                                            && npctk.NGAY_NOP <= pNgaythu_den
                                            select npctk).ToList();
                }
                if (i_ttrang_no == 1)//da cham
                {

                    npc_hdon_tkhoan_list = (from npctk in CMIS2.DB.NPC_HDON_TKHOAN
                                            where npctk.MA_DVIQLY == strMadvi
                                            && npctk.TINH_TRANG == 1
                                            && (npctk.MA_NHANG == strMaNH || strMaNH.Length == 0)
                                            && npctk.NGAY_NOP >= pNgaythu_tu
                                            && npctk.NGAY_NOP <= pNgaythu_den
                                            select npctk).ToList();
                }
                if (i_ttrang_no == 2)//tat ca
                {
                    npc_hdon_tkhoan_list = (from npctk in CMIS2.DB.NPC_HDON_TKHOAN
                                            where npctk.MA_DVIQLY == strMadvi
                                                //&& npctk.TINH_TRANG == 1
                                            && (npctk.MA_NHANG == strMaNH || strMaNH.Length == 0)
                                            && npctk.NGAY_NOP >= pNgaythu_tu
                                            && npctk.NGAY_NOP <= pNgaythu_den
                                            select npctk).ToList();
                }
                if (npc_hdon_tkhoan_list.Count == 0)
                { return null; }
                arrID_HDON = npc_hdon_tkhoan_list.Select(c => c.ID_HDON).ToArray();
                cn_theodoino_list = (from npcno in CMIS2.DB.CN_THEODOINO
                                     where npcno.MA_DVIQLY == strMadvi
                                     && arrID_HDON.Contains(npcno.ID_HDON)
                                     && (npcno.MA_PTTT == strMAPTTT || strMAPTTT.Length == 0)
                                     //&& npcno.TIEN_NO == 0 && npcno.THUE_NO == 0
                                     //&& npcno.TTRANG_SSAI == 0
                                     select npcno).ToList();

                npc_gdich_atm_list = (from npcgdich in CMIS2.DB.NPC_GDICH_ATM
                                      where npcgdich.MA_DVIQLY == strMadvi
                                      && arrID_HDON.Contains(npcgdich.ID_HDON)
                                      && npcgdich.MA_HUYGD == null
                                      && npcgdich.NGAY_TTOAN > pNgaythu_tu
                                      && npcgdich.NGAY_TTOAN <= pNgaythu_den
                                      select npcgdich).ToList();

                var dskhthu = from tk in npc_hdon_tkhoan_list
                              join cn in cn_theodoino_list
                              on new { tk.MA_DVIQLY, tk.ID_HDON }
                              equals new { cn.MA_DVIQLY, cn.ID_HDON }
                              join gd in npc_gdich_atm_list
                              on new { tk.MA_DVIQLY, tk.MA_GD }
                              equals new { gd.MA_DVIQLY, gd.MA_GD }
                              orderby cn.MA_SOGCS, cn.STT
                              select new
                              {
                                  CHON = 1,
                                  cn.MA_DVIQLY,
                                  cn.MA_KHANG,
                                  cn.TEN_KHANG,
                                  cn.TTRANG_SSAI,
                                  cn.STT,
                                  cn.DCHI_KHANG,
                                  cn.LOAI_HDON,
                                  cn.ID_HDON,
                                  gd.MA_GD,
                                  cn.MA_PTTT,
                                  cn.TTHAI_GIAO,
                                  THANG_NAM = cn.THANG + "/" + cn.NAM,
                                  cn.MA_SOGCS,
                                  cn.TIEN_NO,
                                  cn.THUE_NO,
                                  TTIEN_NO = cn.THUE_NO + cn.TIEN_NO,
                                  tk.NGAY_NOP,
                                  tk.SO_TIEN,
                                  tk.TIEN_GTGT,
                                  TTIEN_NOP = tk.SO_TIEN + tk.TIEN_GTGT,
                                  tk.TINH_TRANG
                              }
                              ;
                DataSet dsdsthutien = new DataSet();
                DataTable dtdsthutien = new DataTable();
                dtdsthutien = Utility.LINQToDataTable(dskhthu);
                dtdsthutien.TableName = "DSTHU";
                dsdsthutien.Tables.Add(dtdsthutien);
                return dsdsthutien;
            }
            catch
            { return null; }
        }
        public DataSet get_DM_NHANG()
        {
            try
            {
                var nganhang = from p in CMIS2.DB.D_NGAN_HANG
                               select new
                               {
                                   MA_SEARCH = p.MA_NHANG,
                                   SEARCH = p.MA_NHANG + "-" + p.TEN_NHANG,
                                   TEN_SEARCH = p.TEN_NHANG
                               };
                DataSet ds_nganhang = new DataSet();
                DataTable dt_nganhang = new DataTable();
                dt_nganhang = Utility.LINQToDataTable(nganhang);
                ds_nganhang.Tables.Add(dt_nganhang);
                return ds_nganhang;
            }
            catch { return null; }
        }
        public DataSet get_DM_TNGAN(string strMadvi)
        {
            try
            {
                var thungan = from p in CMIS2.DB.D_TNGAN_VIEN
                              where p.MA_DVIQLY == strMadvi
                              select new
                              {
                                  MA_SEARCH = p.MA_TNGAN,
                                  SEARCH = p.MA_TNGAN + "-" + p.TEN_TNGAN,
                                  TEN_SEARCH = p.TEN_TNGAN
                              };
                DataSet ds_thungan = new DataSet();
                DataTable dt_thungan = new DataTable();
                dt_thungan = Utility.LINQToDataTable(thungan);
                ds_thungan.Tables.Add(dt_thungan);
                return ds_thungan;
            }
            catch { return null; }
        }
        public string get_DM_NVIEN(string strMadvi)
        {
            try
            {
                string thungan = (from p in CMIS2.DB.D_TNGAN_VIEN
                                  where p.MA_DVIQLY == strMadvi
                                  select p.MA_TNGAN).First().ToString();
                return thungan;
            }
            catch { return null; }
        }
        public string get_TNVIEN_by_IDHDON(string strMadvi, Int64 pIDHODN)
        {
            try
            {
                var hdgt = (from p in CMIS2.DB.CN_HDON_GTHU
                            where p.MA_DVIQLY == strMadvi
                            && p.ID_HDON == pIDHODN
                            select p).ToList();
                var maxBBANGIAO = hdgt.GroupBy(c => new { c.MA_DVIQLY, c.ID_HDON }).Select(k => new { k.Key.MA_DVIQLY, k.Key.ID_HDON, SO_BBGIAO = k.Max(m => m.SO_BBGIAO) }).ToList();
                var gthu = (from p in hdgt
                            join q in maxBBANGIAO
                            on new { p.MA_DVIQLY, p.ID_HDON, p.SO_BBGIAO } equals new { q.MA_DVIQLY, q.ID_HDON, q.SO_BBGIAO }
                            select p).ToList();
                Int64[] arrSobbgiao;
                arrSobbgiao = gthu.Select(c => c.SO_BBGIAO).ToArray();
                string thungan = (from m in CMIS2.DB.CN_BB_GTHU
                                  where m.MA_DVIQLY == strMadvi
                                  && arrSobbgiao.Contains(m.SO_BBGIAO)
                                  select m.MA_TNGAN).First().ToString();
                return thungan;
            }
            catch { return null; }
        }
        public DataSet get_PHTHUC_TTOAN()
        {
            try
            {
                var pttt = from p in CMIS2.DB.D_PTHUC_TTOAN
                           select new
                           {
                               MA_SEARCH = p.MA_PTTT,
                               SEARCH = p.MA_PTTT + "-" + p.TEN_PTTT,
                               TEN_SEARCH = p.TEN_PTTT
                           };
                DataSet ds_pttt = new DataSet();
                DataTable dt_pttt = new DataTable();
                dt_pttt = Utility.LINQToDataTable(pttt);
                ds_pttt.Tables.Add(dt_pttt);
                return ds_pttt;
            }
            catch { return null; }
        }
        public DataSet get_TIMESPAN(string strMadvi)
        {
            try
            {
                var pttt = from p in CMIS2.DB.S_PARAMETER
                           where p.SUBDIVISIONID == strMadvi
                           && p.NAME == "G_TIMEATM_NPC"
                           select new
                           {
                               GIO = p.PRAVALUE,
                               PHUT = p.STATE
                           };
                DataSet ds_pttt = new DataSet();
                DataTable dt_pttt = new DataTable();
                dt_pttt = Utility.LINQToDataTable(pttt);
                ds_pttt.Tables.Add(dt_pttt);
                return ds_pttt;
            }
            catch { return null; }
        }
        public bool update_chamno_ATM()
        {
            try
            {
                CN_THEODOINO objCN_THEODOINO = CMIS2.DB.CN_THEODOINO.Single(c => c.MA_DVIQLY == info.MA_DVIQLY && c.ID_HDON == info.ID_HDON && c.LOAI_HDON == info.LOAI_HDON && c.TTRANG_SSAI == 0);
                objCN_THEODOINO.TIEN_NO = info.TIEN_NO;
                objCN_THEODOINO.THUE_NO = info.THUE_NO;
                return true;
            }
            catch
            { return false; }
        }
        public bool update_chamno_Cancel(string strMadonvi, string strLoaihdon, long pID_hdon, decimal pTien_nop, decimal pthue_nop)
        {
            try
            {
                CN_THEODOINO objCN_THEODOINO = CMIS2.DB.CN_THEODOINO.Single(c => c.MA_DVIQLY == strMadonvi && c.ID_HDON == pID_hdon && c.LOAI_HDON == strLoaihdon && c.TTRANG_SSAI == 0);
                objCN_THEODOINO.TIEN_NO = pTien_nop;
                objCN_THEODOINO.THUE_NO = pthue_nop;
                return true;
            }
            catch
            { return false; }
        }
        public bool update_TTHAI_GIAO()
        {
            try
            {
                for (int i = 0; i < infolst.Count; i++)
                {
                    CN_THEODOINO objCN_THEODOINO = CMIS2.DB.CN_THEODOINO.Single(c => c.ID_HDON == infolst.ElementAt(i).ID_HDON && c.LOAI_HDON == infolst.ElementAt(i).LOAI_HDON && c.MA_DVIQLY == infolst.ElementAt(i).MA_DVIQLY);
                    objCN_THEODOINO.TTHAI_GIAO = infolst.ElementAt(i).TTHAI_GIAO;
                    objCN_THEODOINO.LAN_GIAO = infolst.ElementAt(i).LAN_GIAO;
                }
                return true;
            }
            catch (Exception)
            {
                //CMIS2.ResetDB(); 
                return false;
            }
        }
        #endregion

        # region Chamno-OFFLINE
        public DataSet get_DSNO(DataSet ds, string strMadonvi)
        {
            try
            {
                List<CN_THEODOINO> lstno = new List<CN_THEODOINO>();
                var lst_NGAYCHAMNO = (from a in ds.Tables[0].AsEnumerable()
                                      select new
                                      {
                                          MA_DVIQLY = a["MA_DVIQLY"].ToString(),
                                          ID_HDON = Convert.ToInt64(a["ID_HDON"]),
                                          TIEN_NOP = Convert.ToDecimal(a["TIEN_NOP"]),
                                          THUE_NOP = Convert.ToDecimal(a["THUE_NOP"]),
                                          SO_BBGIAO = Convert.ToInt64(a["SO_BBGIAO"]),
                                          MA_TNGAN = a["MA_TNGAN"].ToString(),
                                          NGAY_NOP = Convert.ToDateTime(a["NGAY_CHAMNO"]),
                                          MA_TNGAN_THUHO = a["MA_TNGAN_THUHO"].ToString(),
                                          TINHTRANGIN = a["TINHTRANGIN"].ToString()
                                      }).ToList();



                //lst_NGAYCHAMNO = ds.Tables["NPC_NGAY_CHAMNO"].Select("0=0").ToList(); ;
                Int64[] arrID;
                arrID = lst_NGAYCHAMNO.Select(c => c.ID_HDON).ToArray();

                lstno = (from p in CMIS2.DB.CN_THEODOINO
                         //join q in lst_NGAYCHAMNO on new {p.MA_DVIQLY, p.ID_HDON} equals new {q.MA_DVIQLY,q.ID_HDON}
                         where p.MA_DVIQLY == strMadonvi
                         && arrID.Contains(p.ID_HDON)
                         select p).ToList();

                var KQ = (from p in lstno
                          join q in lst_NGAYCHAMNO on new { p.MA_DVIQLY, p.ID_HDON } equals new { q.MA_DVIQLY, q.ID_HDON }
                          orderby p.MA_SOGCS, p.STT
                          select new
                          {
                              CHON = "True",
                              p.MA_DVIQLY,
                              p.ID_HDON,
                              p.MA_KHANG,
                              p.TEN_KHANG,
                              p.DCHI_KHANG,
                              p.MA_SOGCS,
                              p.NAM,
                              p.THANG,
                              p.KY,
                              THANG_NAM = p.THANG + "/" + p.NAM,
                              p.TTRANG_SSAI,
                              p.MA_PTTT,
                              p.TIEN_NO,
                              p.THUE_NO,
                              TTIEN_NO = (p.TIEN_NO + p.THUE_NO).ToString("## ### ### ###"),
                              SO_TIEN = q.TIEN_NOP,
                              TIEN_GTGT = q.THUE_NOP,
                              TTIEN_NOP = (q.TIEN_NOP + q.THUE_NOP).ToString("## ### ### ###"),
                              NGAY_NOP = q.NGAY_NOP.ToShortDateString(),
                              q.MA_TNGAN,
                              q.SO_BBGIAO,
                              p.STT,
                              p.LOAI_HDON,
                              q.MA_TNGAN_THUHO,
                              q.TINHTRANGIN
                          }
                         ).ToList();
                DataTable dtno = new DataTable();
                DataSet dsno = new DataSet();

                dtno = Utility.LINQToDataTable(KQ);
                dtno.TableName = "DSNO";
                dsno.Tables.Add(dtno);
                return dsno;
            }
            catch
            { return null; }
        }
        public DataSet get_DSCHAMNO_OFFLINE(string strMadvi, string strMatngan, Int16 ithanght, Int16 inamht, int ilsu, DateTime? pNgaythu_tu, DateTime? pNgaythu_den)
        {
            try
            {
                List<D_TNGAN_VIEN> d_tnganvien_lst = new List<D_TNGAN_VIEN>();
                DataSet ds = new DataSet();
                DataTable dt = new DataTable();
                CMIS2.ResetDB();
                List<NPC_CHAMNO_OFFLINE> npc_chamnooffline_list = new List<NPC_CHAMNO_OFFLINE>();
                Int64[] arrID_HDON;
                d_tnganvien_lst = (from q in CMIS2.DB.D_TNGAN_VIEN
                                   where q.MA_DVIQLY == strMadvi
                                   select q).ToList();
                npc_chamnooffline_list = (from p in CMIS2.DB.NPC_CHAMNO_OFFLINE
                                          where p.MA_DVIQLY == strMadvi
                                          && p.THANG_HT == ithanght && p.NAM_HT == inamht
                                          && (p.MA_TNGAN.ToUpper() == strMatngan.ToUpper())
                                          && p.NGAY_CHAMNO >= pNgaythu_tu
                                          && p.NGAY_CHAMNO <= pNgaythu_den
                                          select p).ToList();
                arrID_HDON = npc_chamnooffline_list.Select(c => c.ID_HDON).ToArray();
                if (ilsu == 0)
                {
                    var lstno = (from p in CMIS2.DB.CN_THEODOINO
                                 where p.MA_DVIQLY == strMadvi
                                 && arrID_HDON.Contains(p.ID_HDON)
                                 select p).ToList();
                    var KQ = (from m in npc_chamnooffline_list
                              join n in lstno
                              on new { m.MA_DVIQLY, m.ID_HDON } equals new { n.MA_DVIQLY, n.ID_HDON }
                              orderby n.MA_SOGCS, n.STT
                              select new
                              {
                                  m.MA_DVIQLY,
                                  m.ID_HDON,
                                  m.MA_TNGAN,
                                  TEN_TNGAN = (from k in d_tnganvien_lst where k.MA_TNGAN == m.MA_TNGAN select k.TEN_TNGAN).FirstOrDefault(),
                                  m.SO_BBGIAO,
                                  m.THANG_HT,
                                  m.NAM_HT,
                                  m.TIEN_NOP,
                                  m.THUE_NOP,
                                  TTIEN_NOP = m.TIEN_NOP + m.THUE_NOP,
                                  m.NGAY_CHAMNO,
                                  n.MA_KHANG,
                                  n.TEN_KHANG,
                                  n.DCHI_KHANG,
                                  n.KY,
                                  n.THANG,
                                  n.NAM,
                                  n.MA_SOGCS,
                                  n.STT,
                                  n.LOAI_HDON
                              }
                              ).ToList();
                    dt = Utility.LINQToDataTable(KQ);
                    dt.TableName = "DSCHAMNO";
                }
                else
                {
                    var lstno = (from p in CMIS2.DB.CN_THEODOINO_LSU
                                 where p.MA_DVIQLY == strMadvi
                                 && p.THANG_HT == ithanght && p.NAM_HT == inamht
                                 && arrID_HDON.Contains(p.ID_HDON)
                                 select p).ToList();
                    var KQ = (from m in npc_chamnooffline_list
                              join n in lstno
                              on new { m.MA_DVIQLY, m.ID_HDON } equals new { n.MA_DVIQLY, n.ID_HDON }
                              orderby n.MA_SOGCS, n.STT
                              select new
                              {
                                  m.MA_DVIQLY,
                                  m.ID_HDON,
                                  m.MA_TNGAN,
                                  m.SO_BBGIAO,
                                  m.THANG_HT,
                                  m.NAM_HT,
                                  m.TIEN_NOP,
                                  m.THUE_NOP,
                                  TTIEN_NOP = m.TIEN_NOP + m.THUE_NOP,
                                  m.NGAY_CHAMNO,
                                  n.MA_KHANG,
                                  n.TEN_KHANG,
                                  n.DCHI_KHANG,
                                  n.KY,
                                  n.THANG,
                                  n.NAM,
                                  n.MA_SOGCS,
                                  n.STT,
                                  n.LOAI_HDON
                              }
                              ).ToList();
                    dt = Utility.LINQToDataTable(KQ);
                    dt.TableName = "DSCHAMNO";
                }
                ds.Tables.Add(dt);
                return ds;
            }
            catch
            { return null; }
        }
        public DataSet get_DSTHUTIEN(string strMadvi, string strMatngan, Int16 ithanght, Int16 inamht, DateTime? pNgaythu_tu, DateTime? pNgaythu_den, DateTime? pNgaythu, int Danhsach, int IN_BC, string lanin)
        {
            try
            {
                List<D_TNGAN_VIEN> d_tnganvien_lst = new List<D_TNGAN_VIEN>();
                DataSet ds = new DataSet();
                DataTable dt = new DataTable();
                CMIS2.ResetDB();
                List<NPC_CHAMNO_OFFLINE> npc_chamnooffline_list = new List<NPC_CHAMNO_OFFLINE>();
                Int64[] arrID_HDON;
                d_tnganvien_lst = (from q in CMIS2.DB.D_TNGAN_VIEN
                                   where q.MA_DVIQLY == strMadvi
                                   select q).ToList();
                if (Danhsach == 0)
                {
                    if (IN_BC == 0)
                    {
                        npc_chamnooffline_list = (from p in CMIS2.DB.NPC_CHAMNO_OFFLINE
                                                  where p.MA_DVIQLY == strMadvi
                                                  && p.THANG_HT == ithanght && p.NAM_HT == inamht
                                                  && (p.MA_TNGAN.ToUpper() == strMatngan.ToUpper() || strMatngan.Length == 0)
                                                  && p.NGAY_CHAMNO >= pNgaythu_tu
                                                  && p.NGAY_CHAMNO <= pNgaythu_den
                                                  //&& p.TRANGTHAI == 0
                                                  select p).ToList();
                    }
                    if (IN_BC == 1)
                    {
                        npc_chamnooffline_list = (from p in CMIS2.DB.NPC_CHAMNO_OFFLINE
                                                  where p.MA_DVIQLY == strMadvi
                                                  && p.THANG_HT == ithanght && p.NAM_HT == inamht
                                                  && (p.MA_TNGAN.ToUpper() == strMatngan.ToUpper() || strMatngan.Length == 0)
                                                  && p.NGAY_CHAMNO == pNgaythu
                                                  && p.TINHTRANGIN == lanin
                                                  //&& p.TRANGTHAI == 0
                                                  select p).ToList();
                    }
                    if (IN_BC == 2)
                    {
                        npc_chamnooffline_list = (from p in CMIS2.DB.NPC_CHAMNO_OFFLINE
                                                  where p.MA_DVIQLY == strMadvi
                                                  && p.THANG_HT == ithanght && p.NAM_HT == inamht
                                                  && (p.MA_TNGAN.ToUpper() == strMatngan.ToUpper() || strMatngan.Length == 0)
                                                  && p.NGAY_CHAMNO == pNgaythu
                                                  && p.TINHTRANGIN == lanin
                                                  //&& p.TRANGTHAI == 0
                                                  select p).ToList();
                    }
                }
                if (Danhsach == 1)
                {
                    if (IN_BC == 0)
                    {
                        npc_chamnooffline_list = (from p in CMIS2.DB.NPC_CHAMNO_OFFLINE
                                                  where p.MA_DVIQLY == strMadvi
                                                  && p.THANG_HT == ithanght && p.NAM_HT == inamht
                                                  && (p.MA_TNGAN_THUHO.ToUpper() == strMatngan.ToUpper())
                                                  && p.NGAY_CHAMNO >= pNgaythu_tu
                                                  && p.NGAY_CHAMNO <= pNgaythu_den
                                                  //&& p.TRANGTHAI == 0
                                                  select p).ToList();
                    }
                }
                if (Danhsach == 2)
                {
                    if (IN_BC == 0)
                    {
                        npc_chamnooffline_list = (from p in CMIS2.DB.NPC_CHAMNO_OFFLINE
                                                  where p.MA_DVIQLY == strMadvi
                                                  && p.THANG_HT == ithanght && p.NAM_HT == inamht
                                                  && (p.MA_TNGAN_THUHO.ToUpper() != strMatngan.ToUpper())
                                                  && (p.MA_TNGAN.ToUpper() == strMatngan.ToUpper())
                                                  && p.NGAY_CHAMNO >= pNgaythu_tu
                                                  && p.NGAY_CHAMNO <= pNgaythu_den
                                                  //&& p.TRANGTHAI == 0
                                                  select p).ToList();
                    }
                }
                // in ton cuoi thang
                if (Danhsach == 3)
                {
                    if (IN_BC == 0)
                    {
                        npc_chamnooffline_list = (from p in CMIS2.DB.NPC_CHAMNO_OFFLINE
                                                  where p.MA_DVIQLY == strMadvi
                                                  && p.THANG_HT == ithanght && p.NAM_HT == inamht
                                                  && (p.MA_TNGAN_THUHO.ToUpper() != strMatngan.ToUpper())
                                                  && p.NGAY_CHAMNO >= pNgaythu_tu
                                                  && p.NGAY_CHAMNO <= pNgaythu_den
                                                  //&& p.TRANGTHAI == 0
                                                  select p).ToList();
                    }
                }
                List<CN_THEODOINO> cn = new List<CN_THEODOINO>();
                List<CN_THEODOINO> lstno = new List<CN_THEODOINO>();
                cn = (from c in CMIS2.DB.CN_THEODOINO
                      where c.MA_DVIQLY == strMadvi
                      select c).ToList();
                lstno = (from p in cn
                             join n in npc_chamnooffline_list
                              on new { p.MA_DVIQLY, p.ID_HDON } equals new { n.MA_DVIQLY, n.ID_HDON }
                          select p).ToList();
                var KQ = (from m in npc_chamnooffline_list
                          join n in lstno
                          on new { m.MA_DVIQLY, m.ID_HDON } equals new { n.MA_DVIQLY, n.ID_HDON }
                          orderby n.MA_SOGCS, n.STT
                          select new
                          {
                              m.ID_OFFLINE,
                              m.MA_DVIQLY,
                              m.ID_HDON,
                              m.MA_TNGAN,
                              TEN_TNGAN = (from k in d_tnganvien_lst where k.MA_TNGAN == m.MA_TNGAN select k.TEN_TNGAN).FirstOrDefault(),
                              m.SO_BBGIAO,
                              m.THANG_HT,
                              m.NAM_HT,
                              m.TIEN_NOP,
                              m.THUE_NOP,
                              TTIEN_NOP = m.TIEN_NOP + m.THUE_NOP,
                              m.NGAY_CHAMNO,
                              n.MA_KHANG,
                              n.TEN_KHANG,
                              n.DCHI_KHANG,
                              n.KY,
                              n.THANG,
                              n.NAM,
                              n.MA_SOGCS,
                              n.STT,
                              n.LOAI_HDON
                          }
                          );
                dt = Utility.LINQToDataTable(KQ);
                dt.TableName = "DSTHUTIEN";
                ds.Tables.Add(dt);
                return ds;
            }
            catch
            { return null; }
        }
        public bool update_chamno_OFFLINE()
        {
            try
            {
                CN_THEODOINO objCN_THEODOINO = CMIS2.DB.CN_THEODOINO.Single(c => c.MA_DVIQLY == info.MA_DVIQLY && c.ID_HDON == info.ID_HDON && c.LOAI_HDON == info.LOAI_HDON && c.TTRANG_SSAI == 0);
                objCN_THEODOINO.TIEN_NO = info.TIEN_NO;
                objCN_THEODOINO.THUE_NO = info.THUE_NO;
                return true;
            }
            catch
            {return false; }
        }
        #endregion

    }
}
